package cs236369.hw5;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;

/**
 * This class handles the connections to the databases
 * 
 * @author Yuval and Eddie
 * 
 */
public class DBConnection {

	// ## local variables ##
	static final int transactionRetriesMaxNumber = 10;		// maximum number of retries a transaction
															// will try before throwing SQLException

	/*
	 * addUser comment
	 * ---------------
	 * Although we need to check that the user doesn't exist in the database, and it might be created
	 * by another transaction during the running of this transaction, this method doesn't acquire a lock,
	 * because we don't want to lock the whole table (if we lock- no other user can be added or removed
	 * while this transaction run). We can do that because we define username as a primary key of table 
	 * users, therefore postgreSQL ensures us that if a username is added twice, we will get an exception,
	 * and the transaction will not succeed.
	 */
	/**
	 * Adds a user to the database
	 * @param user - the user to add
	 * @throws CrowdsourcingException if the user is not legal (missing basic information)
	 * @throws SQLException if SQL query fails
	 */
	public void addUser(User user) throws CrowdsourcingException, SQLException {
		PreparedStatement statement1 = null;
		ResultSet rs = null;
		Connection connection = null;

		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(user.getUserName().isEmpty() || user.getPassword().isEmpty()){
				throw new CrowdsourcingException("missingUserInfo","User is missing username or password");
			}
			
			//start transaction
			connection.createStatement().execute("begin");
				
			//check the user is not in the database and lock the row so it won't be deleted
			statement1 = connection.prepareStatement("SELECT username FROM users WHERE username=? FOR UPDATE");
			statement1.setString(1, user.getUserName());
			rs=statement1.executeQuery();
			if (rs.next()!=false) {
				throw new CrowdsourcingException("userExists","Username already taken");
			}
			rs.close();
			
			// prepare statement string
			String strTable = "INSERT INTO users(username,password,isAdmin";
			String strValues = "VALUES(?,?,?";
			// for each variable that is not null, add it to the statement string
			if(user.getFirstName()!=null){
				strTable += ",firstname";
				strValues += ",?";
			}
			if(user.getLastName()!=null){
				strTable += ",lastname";
				strValues += ",?";
			}
			if(user.getBirthDate()!=null){
				strTable += ",birthdate";
				strValues += ",?";
			}
			if(user.getPicPath()!=null){
				strTable += ",picpath";
				strValues += ",?";
			}
			if(user.getEmailAddress()!=null){
				strTable += ",email";
				strValues += ",?";
			}
			strTable += ") ";
			strValues += ")";
			
			statement1 = connection.prepareStatement(strTable + strValues);
			// populate values
			statement1.setString(1, user.getUserName());
			statement1.setString(2, user.getPassword());
			statement1.setBoolean(3, user.getIsAdmin());
			
			int i=3;	//used to count ? when populating values
			if(user.getFirstName()!=null){
				i++;
				statement1.setString(i, user.getFirstName());
			}
			if(user.getLastName()!=null){
				i++;
				statement1.setString(i, user.getLastName());
			}
			if(user.getBirthDate()!=null){
				i++;
				statement1.setDate(i, new java.sql.Date(user.getBirthDate().getTime()));	
			}
			if(user.getPicPath()!=null){
				i++;
				statement1.setString(i, user.getPicPath());
			}
			if(user.getEmailAddress()!=null){
				i++;
				statement1.setString(i, user.getEmailAddress());
			}
			//commit changes
			statement1.executeUpdate();
			connection.createStatement().execute("commit");

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Deletes a user from the database
	 * @param userName - the user the delete
	 * @throws CrowdsourcingException - if user is empty or null
	 * @throws SQLException if SQL query fails
	 */
	public void delUser(String userName) throws CrowdsourcingException, SQLException {
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		String consIDs="";
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);

			if(null==userName || userName.isEmpty()){
				throw new CrowdsourcingException("emptyString","User name is empty or null");
			}
			//start transaction
			connection.createStatement().execute("begin");
			
			// if the user was registered to events- decrement the registration counter in these events
			statement1 = connection.prepareStatement("UPDATE event_reg_status SET currentregs=currentregs-1 " +
					"WHERE mid IN (SELECT mid FROM registration WHERE username=?)");
			statement1.setString(1, userName);
						
			// get user's accepted consensuses so we can handle them later
			statement2 = connection.prepareStatement("SELECT consid from consensus_accepts WHERE username=?");
			statement2.setString(1, userName);
			rs=statement2.executeQuery();
			//get first consensus
			if(rs.next()){
				consIDs+=rs.getInt("consid");
			}
			//get all other consensuses
			while(rs.next()){
				consIDs+=","+rs.getInt("consid");
			}
			rs.close();
			
			/* 
			 * Delete user.
			 * Note: the messages, events and registrations of the deleted user are deleted automatically
			 * by postgres because we added foreign keys in the tables and added ON DELETE CASCADE.
			 */
			statement2 = connection.prepareStatement("DELETE FROM users WHERE username=?");
			statement2.setString(1, userName);
					
			//commit changes
			statement1.executeUpdate();
			statement2.executeUpdate();
			connection.createStatement().execute("commit");
			
			//handle consensuses
			consensusStatusHandler(consIDs);

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally{
			try {
				rs.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				statement1.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				statement2.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	
	/**
	 * Updates a user in the database
	 * @param user - the user to update
	 * @throws CrowdsourcingException if the user is not legal (missing basic information)
	 * @throws SQLException if SQL query fails
	 */
	public void updateUser(User user) throws CrowdsourcingException, SQLException {
		PreparedStatement statement1 = null;
		ResultSet rs = null;
		Connection connection = null;

		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(user.getUserName().isEmpty() || user.getPassword().isEmpty()){
				throw new CrowdsourcingException("missingUserInfo","User is missing username or password");
			}
			
			//start transaction
			connection.createStatement().execute("begin");
				
			//check that the user is in the database
			statement1 = connection.prepareStatement("SELECT username FROM users WHERE username=?");
			statement1.setString(1, user.getUserName());
			rs=statement1.executeQuery();
			if (rs.next()!=true) {
				throw new CrowdsourcingException("userNotExists","There is not such user");
			}
			
			// prepare statement string
			String str = "UPDATE users SET password=?,isAdmin=?";
			// for each variable that is not null, add it to the statement string
			if(user.getFirstName()!=null){
				str += ",firstname=?";
			}
			if(user.getLastName()!=null){
				str += ",lastname=?";
			}
			if(user.getBirthDate()!=null){
				str += ",birthdate=?";
			}
			if(user.getPicPath()!=null){
				str += ",picpath=?";
			}
			if(user.getEmailAddress()!=null){
				str += ",email=?";
			}
			str += " WHERE username=?";
			
			statement1 = connection.prepareStatement(str);
			// populate values
			statement1.setString(1, user.getPassword());
			statement1.setBoolean(2, user.getIsAdmin());
			
			int i=2;	//used to count ? when populting values
			if(user.getFirstName()!=null){
				i++;
				statement1.setString(i, user.getFirstName());
			}
			if(user.getLastName()!=null){
				i++;
				statement1.setString(i, user.getLastName());
			}
			if(user.getBirthDate()!=null){
				i++;
				statement1.setDate(i, new java.sql.Date(user.getBirthDate().getTime()));	
			}
			if(user.getPicPath()!=null){
				i++;
				statement1.setString(i, user.getPicPath());
			}
			if(user.getEmailAddress()!=null){
				i++;
				statement1.setString(i, user.getEmailAddress());
			}
			//insert WHERE ? value
			i++;
			statement1.setString(i, user.getUserName());
			// execute and commit changes
			statement1.executeUpdate();
			connection.createStatement().execute("commit");

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	
	/**
	 * Adds a regular post to the database (not an event)
	 * @param post - the user to add
	 * @return the post's ID as generated by the database. Returns -1 if an SQLException occurred
	 * @throws CrowdsourcingException if the user that posted the post doesn't exist anymore
	 */
	public int addPost(Post post) throws CrowdsourcingException {
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		int messageID = -1;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(post==null){
				throw new CrowdsourcingException("nullParameter", "Post is an illegal null parameter"); 
			}
			
			//start transaction
			connection.createStatement().execute("begin");
				
			/*
			 * When we create a post, we must verify the user that posted the post exists, and also lock
			 * the row of the user in the users table so that it won't be deleted during the transaction.
			 * SELECT FOR SHARE locks the selection for writes, but enables multiple reads.  
			 */
			statement1 = connection.prepareStatement("SELECT username FROM users WHERE username=? FOR SHARE");

			statement1.setString(1, post.getUserName());
			rs=statement1.executeQuery();
			if (rs.next()==false) {
				connection.createStatement().execute("rollback");
				throw new CrowdsourcingException("userNotExists","The user that posted this post doesn't exist anymore");
			}
			rs.close();
			
			// get the ID of the new message from the database
			rs = connection.createStatement().executeQuery("SELECT nextval('messages_mid_seq')");
			rs.next();
			messageID = rs.getInt(1);
			
			// insert message query
			statement2 = connection.prepareStatement("INSERT INTO messages(mid,username,mdate,geotag,type," +
					"content,latnum,longnum) " +
					"VALUES(" + messageID + ",?,?,ST_GeographyFromText('POINT(" + post.getLocation().getLat() + 
					" " + post.getLocation().getLong() + ")'),0,?,?,?)");	//type 0 means regular post (not event)
			
			// populate values
			statement2.setString(1,post.getUserName());
			statement2.setDate(2, new java.sql.Date(post.getDate().getTime()));	
			statement2.setString(3, post.getContent());	
			statement2.setDouble(4, post.getLocation().getLat());
			statement2.setDouble(5, post.getLocation().getLong());

			// execute and commit changes
			statement2.executeUpdate();
			connection.createStatement().execute("commit");
			return messageID;

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return -1;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement2!=null)
					statement2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	
	/**
	 * Deletes a regular post from the database
	 * @param postID - the post to be deleted
	 * @throws SQLException if SQL query fails
	 */
	public void delPost(int postID) throws SQLException {
		PreparedStatement statement = null;
		Connection connection = null;

		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);

			//start transaction
			connection.createStatement().execute("begin");
			statement = connection.prepareStatement("DELETE FROM messages WHERE mid=?");
			statement.setInt(1, postID);
			//commit changes
			statement.executeUpdate();
			connection.createStatement().execute("commit");

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally{
			try {
				//close statement 
				statement.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Adds an event to the database
	 * Also updates the ID of all the consensuses that belong to this new event
	 * @param post - the user to add
	 * @return the event's ID as generated by the database. Returns -1 if an SQLException occurred
	 * @throws CrowdsourcingException if the user that posted the event doesn't exist anymore
	 */
	public int addEvent(EventPost event) throws CrowdsourcingException {
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		PreparedStatement statement3 = null;
		PreparedStatement statement4 = null;
		PreparedStatement statement5 = null;
		ResultSet rs = null;
		Connection connection = null;
		int eventID = -1;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(event==null){
				throw new CrowdsourcingException("nullParameter", "Event is an illegal null parameter"); 
			}
			
			//start transaction
			connection.createStatement().execute("begin");
				
			/*
			 * When we create a post, we must verify the user that posted the post exists, and also lock
			 * the row of the user in the users table so that it won't be deleted during the transaction.
			 * SELECT FOR SHARE locks the selection for writes, but enables multiple reads.  
			 */
			statement1 = connection.prepareStatement("SELECT username FROM users WHERE username=? FOR SHARE");

			statement1.setString(1, event.getUserName());
			rs=statement1.executeQuery();
			if (rs.next()==false) {
				connection.createStatement().execute("rollback");
				throw new CrowdsourcingException("userNotExists","The user that posted this post doesn't exist anymore");
			}
			rs.close();
			
			// get the ID of the new event from the database
			rs = connection.createStatement().executeQuery("SELECT nextval('messages_mid_seq')");
			rs.next();
			eventID = rs.getInt(1);
		
			//insert event to messages table
			statement2 = connection.prepareStatement("INSERT INTO messages(mid,username,mdate,geotag,type,content) " +
					"VALUES(" + eventID + ",?,?,ST_GeographyFromText('POINT(" + event.getLocation().getLat() + 
					" " + event.getLocation().getLong() + ")'),1,?)");	//type 1 means event
			statement2.setString(1,event.getUserName());
			statement2.setDate(2, new java.sql.Date(event.getDate().getTime()));	
			statement2.setString(3, event.getContent());	

			//insert registration variables of this event
			statement3 = connection.prepareStatement("INSERT INTO event_reg_status(mid,maxregs,currentregs) " +
					"VALUES(" +eventID + ",?,1)");		//current regs is 1- the creator of the event is registered
			statement3.setInt(1,event.getMaxRegs());
			
			//register the creator of the event
			statement4 = connection.prepareStatement("INSERT INTO registration(mid,username) " +
					"VALUES(" + eventID + ",?)");
			statement4.setString(1,event.getUserName());
			
			//add all consensus demands that were requested by the creator
			statement5 = connection.prepareStatement("INSERT INTO consensuses(consid,mid," +
					"conscontent,isapproved) VALUES(?,?,?,?)");
			
			//execute (the writing locks are acquired here)
			statement2.executeUpdate();
			statement3.executeUpdate();
			statement4.executeUpdate();
			
			//add consensuses
			for(Consensus con : event.getConsensuses()){
				// get the ID of the new consensus from the database
				rs = connection.createStatement().executeQuery("SELECT nextval('consensuses_consid_seq')");
				rs.next();
				con.setConsID(rs.getInt(1));
				statement5.setInt(1,con.getConsID());
				statement5.setInt(2,eventID);
				statement5.setString(3,con.getContent());
				statement5.setBoolean(4,con.getIsApproved());
				statement5.executeUpdate();
			}
			
			//commit changes 
			connection.createStatement().execute("commit");
			return eventID;
			
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return -1;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement2!=null)
					statement2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement3!=null)
					statement4.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement4!=null)
					statement5.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement5!=null)
					statement5.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Deletes an event from the database
	 * @param eventID - the event to be deleted
	 * @throws SQLException if SQL query fails
	 */
	public void delEvent(int eventID) throws SQLException {
		/*
		 * Because event is a type of a post, and deleting a post automatically deletes from the database
		 * all its dependencies (such as event registrations etc...), we can simply call delPost.
		 * The automatic deletion is done by Postgres because of the foreign keys.
		 */
		delPost(eventID);
	}
	
	
	/**
	 * Insert a registration of a user to an event
	 * @param username
	 * @param eventID
	 * @throws CrowdsourcingException if parameter is not valid, if there is no event with such ID, no user
	 * with such username, or if there are too many registrations to the event (no free room)
	 * @throws SQLException if SQL query fails
	 */
	public void registerToEvent(String username,int eventID) throws CrowdsourcingException, SQLException {
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		int currentregs = 0;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(username==null || username.isEmpty()){
				throw new CrowdsourcingException("invalidParameter", "Username is empty or null"); 
			}
			
			//start transaction
			connection.createStatement().execute("begin");
			
			/* check if there is enough room for registration and lock the row to stop concurrent registrations
			 * and also to enable incrementing the 
			 */
			statement1 = connection.prepareStatement("SELECT maxregs,currentregs FROM event_reg_status WHERE " +
					"mid=? FOR UPDATE");
			statement1.setInt(1, eventID);
			rs=statement1.executeQuery();
			if (rs.next()==false) {
				connection.createStatement().execute("rollback");
				throw new CrowdsourcingException("notSuchEvent","There is no event with this ID");
			}
			// if max registrations is smaller or equal current registrations- don't allow 
			currentregs=rs.getInt(2);
			if(rs.getInt(1) <= currentregs){
				connection.createStatement().execute("rollback");
				throw new CrowdsourcingException("tooManyRegistrations","There is no event with this ID");
			}
			rs.close();
						
			// insert registration
			statement1 = connection.prepareStatement("INSERT INTO registration(mid,username) VALUES(?,?)");
			statement1.setInt(1,eventID);
			statement1.setString(2,username);	

			// update registrations number
			statement2 = connection.prepareStatement("UPDATE event_reg_status SET currentregs=? WHERE mid=?");
			statement2.setInt(1,currentregs + 1);
			statement2.setInt(2,eventID);
			
			// execute and commit changes
			statement1.executeUpdate();
			statement2.executeUpdate();
			connection.createStatement().execute("commit");

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement2!=null)
					statement2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}

	/**
	 * Removes a registration of a user to an event. Also removes user's approvals or declines of consensuses
	 * @param username
	 * @param eventID
	 * @throws CrowdsourcingException if parameter is not valid, or if the user wasn't registered
	 * to the event from the first place
	 * @throws SQLException if SQL query fails
	 */
	public void unregisterFromEvent(String username,int eventID) throws CrowdsourcingException, SQLException {
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		String consIDs = "";
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(username==null || username.isEmpty()){
				throw new CrowdsourcingException("invalidParameter", "Username is empty or null"); 
			}
			
			//start transaction
			connection.createStatement().execute("begin");
			
			// decrement event's number of registrations
			statement1 = connection.prepareStatement("UPDATE event_reg_status SET currentregs=currentregs-1 " +
					"WHERE mid=?");
			statement1.setInt(1, eventID);
			
			// get user's accepted consensuses of this event so we can handle them later
			statement2 = connection.prepareStatement("SELECT consid from consensus_accepts ca,consensuses c " +
					"WHERE username=? and ca.consid=c.consid and c.mid=?");
			statement2.setString(1, username);
			statement2.setInt(1, eventID);
			rs=statement2.executeQuery();
			
			//get first consensus
			if(rs.next()){
				consIDs+=rs.getInt("consid");
			}
			//get all other consensuses
			while(rs.next()){
				consIDs+=","+rs.getInt("consid");
			}
			rs.close();

			// remove all approved consensuses of the user in this event
			if(consIDs!=""){
				statement2 = connection.prepareStatement("DELETE FROM consensus_accepts WHERE username=? " +
						"and consid IN(?)");
				statement2.setString(1, username);
				statement2.setString(2, consIDs);
				statement2.executeUpdate();
			}
			
			// execute and commit changes
			statement1.executeUpdate();
			connection.createStatement().execute("commit");
			
			// handle consensuses
			consensusStatusHandler(consIDs);

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement2!=null)
					statement2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}

	
	/*
	 * This comment is not longer actual, because of the new mechanism of consensus handling (see function
	 * acceptConsensus) but is left here nevertheless so that future edits will be aware of this issue:
	 * 
	 * We need to make this transaction serializable to avoid phantom reads, to prevent this scenario:
	 * Let's say we have 3 people registered to the event (therefore need 3 people to approve the 
	 * consensus request), and that 1 user already approved it.
	 * Now the other 2 users want to accept the consensus, and 2 transactions start. Both transactions
	 * read and count that only 1 person approved the consensus, and therefore none of them changes the
	 * status of the consensus to approved, although the second commiting transaction should.
	 * Choosing serializable will cause the second transaction to fail, and we should try it again.
	 * 
	 * 	connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
	 */
	/**
	 * Insert the acceptance of the consensus by the user
	 * @param username - the user accepting the consensus
	 * @param consID - the consensus ID
	 * @param timesToTry - Indicates how many times the function should retry initiating the transaction in case
	 * of SQLException before actually throwing SQLException. Max number is {@value #transactionRetriesMaxNumber}.
	 * This is useful in case of a failure caused by phantom reads.
	 * @throws CrowdsourcingException if parameter is not valid, if there is no event with such ID, no user
	 * with such username, or if there are too many registrations to the event (no free room)
	 * @throws SQLException if SQL query fails
	 */
	public void acceptConsensus(String username,int consID, int timesToTry) throws CrowdsourcingException, SQLException {
		PreparedStatement statement1 = null;
		ResultSet rs = null;
		Connection connection = null;

		if(timesToTry>transactionRetriesMaxNumber){
			timesToTry=transactionRetriesMaxNumber;
		}
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(username==null || username.isEmpty()){
				throw new CrowdsourcingException("invalidParameter", "Username is empty or null"); 
			}
			
			//start transaction
			connection.createStatement().execute("begin");
			
			/*
			 *  check that the user is registered to the event that the consensus belongs to
			 */
			statement1 = connection.prepareStatement("SELECT r.mid FROM consensuses c,registration r " +
					"WHERE c.consid=? and c.mid=r.mid and r.username=?" );
			statement1.setInt(1, consID);
			statement1.setString(2, username);     
			rs=statement1.executeQuery();
			if (rs.next()==false) {
				connection.createStatement().execute("rollback");
				throw new CrowdsourcingException("userNotRegistered","User is not registered to the event");
			}
			// get the event ID 
			rs.close();
						
			// insert user acceptance
			statement1 = connection.prepareStatement("INSERT INTO consensus_accepts(consid,username) VALUES(?,?)");
			statement1.setInt(1,consID);
			statement1.setString(2,username);		
			statement1.executeUpdate();
			
			// commit changes
			connection.createStatement().execute("commit");
			
			// handle consensuses
			consensusStatusHandler("" + consID);

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			if(timesToTry>0){
				// try running the transaction again 
				acceptConsensus(username,consID, timesToTry - 1);
			}
			else {
				throw e;
			}
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Deletes the previous user's acceptance of the consensus
	 * @param username
	 * @param consID
	 * @throws CrowdsourcingException if user is not registered to the event or username is empty or null
	 * @throws SQLException
	 */
	public void unAcceptConsensus(String username,int consID) throws CrowdsourcingException, SQLException {
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
		
			if(username==null || username.isEmpty()){
				throw new CrowdsourcingException("invalidParameter", "Username is empty or null"); 
			}
			
			//start transaction
			connection.createStatement().execute("begin");
			
			/*
			 *  check that the user is registered to the event that the consensus belongs to
			 */
			statement1 = connection.prepareStatement("SELECT r.mid FROM consensuses c,registration r " +
					"WHERE c.consid=? and c.mid=r.mid and r.username=?" );
			statement1.setInt(1, consID);
			statement1.setString(2, username);     
			rs=statement1.executeQuery();
			if (rs.next()==false) {
				connection.createStatement().execute("rollback");
				throw new CrowdsourcingException("userNotRegistered","User is not registered to the event");
			}
			rs.close();
						
			// remove user acceptance
			statement1 = connection.prepareStatement("DELETE FROM consensus_accepts WHERE consid=? and " +
					"username=?");
			statement1.setInt(1,consID);
			statement1.setString(2,username);		
			statement1.executeUpdate();

			// commit changes
			connection.createStatement().execute("commit");

			//handle the consensus
			consensusStatusHandler("" + consID);
			
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement2!=null)
					statement2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Inserts a consensus to the data base and returns an ID that is generated by the database.
	 * This ID should be used afterwards to set the Consensus' ID with the function setID.
	 * @param cons - the consensus object
	 * @return - a new ID for the consensus
	 * @throws CrowdsourcingException on null parameter
	 */
	public int addConsensus(Consensus cons) throws CrowdsourcingException {
		PreparedStatement statement1 = null;
		ResultSet rs = null;
		Connection connection = null;
		int consID = -1;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			if(cons==null){
				throw new CrowdsourcingException("nullParameter", "cons is an illegal null parameter"); 
			}
			
			//start transaction
			connection.createStatement().execute("begin");
				
			
			// get the ID of the new consensus from the database
			rs = connection.createStatement().executeQuery("SELECT nextval('consensuses_consid_seq')");
			rs.next();
		
			//insert consensus to consensuses table
			statement1 = connection.prepareStatement("INSERT INTO consensuses(consid,mid," +
					"conscontent,isapproved) VALUES(?,?,?,?)");
		
			// get the ID of the new consensus from the database
			rs = connection.createStatement().executeQuery("SELECT nextval('consensuses_consid_seq')");
			rs.next();
			consID = rs.getInt(1);
			
			statement1.setInt(1,consID);
			statement1.setInt(2,cons.getEventID());
			statement1.setString(3,cons.getContent());
			statement1.setBoolean(4,cons.getIsApproved());
			statement1.executeUpdate();
			
			//commit changes 
			connection.createStatement().execute("commit");
			return consID;
			
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return -1;
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Gets a username and password, and checks if this is a valid username-password combination to login
	 * 
	 * @param userName
	 * @param password
	 *            
	 * @return true if there is a user with the password in the database and false if not
	 */
	public boolean verifyLogin(String userName, String password) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT username FROM users WHERE username=? AND password=?");
			statement.setString(1, userName);
			statement.setString(2, password);
			rs = statement.executeQuery();
	
			// check if there is selection
			if(rs.next()){
				return true;
			} else {
				return false;
			}
	
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Gets a username and returns true if its an admin.
	 * 
	 * @param userName
	 *            - unique userName
	 * @return true if user is an admin, false if it is not, or if it doesn't exist.
	 */
	public boolean isAdmin(String userName) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT isadmin FROM users WHERE username=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();	
			
			// check if admin
			while (rs.next()) {
				if (rs.getBoolean(1) == true) {
					return true;
				} else {
					return false;
				}
			}
			return false;
	
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {					e.printStackTrace();
				}	
			}
		}
	}

	/**
	 * Returns a user with all saved details
	 * @param userName
	 * @return User object
	 * @throws CrowdsourcingException - if basic information like userName or password is missing
	 */
	public User getUserDetails(String userName) throws CrowdsourcingException {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT password,firstname,lastname,isadmin," +
					"birthdate,picpath,email FROM users WHERE username=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			
			User user = new User(userName, rs.getString("password"), rs.getString("firstname"), 
					rs.getString("lastname"), rs.getBoolean("isadmin"),rs.getDate("birthdate"),
					rs.getString("picpath"), rs.getString("email"));
			
			return user;
			
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {					
					e.printStackTrace();
				}	
			}
		}
	}

	/**
	 * Returns a set of all user's regular posts (not events)
	 * @param userName
	 * @return  set of all user's regular posts. empty set if user has no posts and null on error
	 */
	public HashSet<Post> getUserRegularPosts(String userName) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT mid,mdate,content,latnum,longnum FROM messages " +
					"WHERE username=? and type=0");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			
			HashSet<Post> postsSet = new HashSet<Post>();
			while(rs.next()){
				Post post = new Post(rs.getString("content"), userName,new Location(rs.getDouble("latnum"),
						rs.getDouble("longnum")),rs.getDate("mdate"));
				post.setPostID(rs.getInt("mid"));
				postsSet.add(post);
			}
			
			return postsSet;
			
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {					
					e.printStackTrace();
				}	
			}
		}
	}
	
	
	/**
	 * Returns a set of all user's events
	 * @param userName
	 * @return  set of all user's events. empty set if user has no events and null on error
	 */
	public HashSet<EventPost> getUserEvents(String userName) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT m.mid,mdate,content,latnum,longnum,maxregs " +
					"FROM messages m,event_reg_status e WHERE username=? and type=1 and m.mid=e.mid");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			
			// create all events
			HashSet<EventPost> eventsSet = new HashSet<EventPost>();
			while(rs.next()){
				EventPost event = new EventPost(rs.getString("content"), userName,new Location(rs.getDouble("latnum"),
						rs.getDouble("longnum")),rs.getDate("mdate"),rs.getInt("maxregs"));
				event.setPostID(rs.getInt("mid"));
				eventsSet.add(event);
			}
			rs.close();
			
			// add consensuses
			for(EventPost event : eventsSet){
				statement = connection.prepareStatement("SELECT consid,conscontent,isapproved FROM consensuses " +
						"WHERE mid=?");
				statement.setInt(1, event.getPostID());
				rs = statement.executeQuery();
				
				while(rs.next()){
					Consensus cons = new Consensus(rs.getBoolean("isapproved"), rs.getString("conscontent"));
					cons.setConsID(rs.getInt("consid"));
					cons.setEventID(event.getPostID());
					event.addConsensus(cons);
				}
				rs.close();
			}
			
			
			return eventsSet;
			
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {					
					e.printStackTrace();
				}	
			}
		}
	}
	
	/**
	 * Finds the user that owns the message
	 * @param postID - the message ID
	 * @return - the username as string or empty string in case of an error
	 * @throws CrowdsourcingException - if the message wasn't found
	 */
	public String getMessageOwner(int postID) throws CrowdsourcingException{
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT username FROM messages WHERE mid=?");
			statement.setInt(1, postID);
			rs = statement.executeQuery();
	
			if(!rs.next()){
				throw new CrowdsourcingException("notFound","Message not found");
			}
			
			return rs.getString("username");
				
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return "";
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	
	/**
	 * Returns all messages that are within radius distance around point loc
	 * @param loc- the middle of the circle
	 * @param distance -  the radius
	 * @param username - empty string brings messages of all users. if not empty- the function will return only
	 * messages of this user
	 * @param bringPosts - true to bring regular posts
	 * @param bringEvents - true to bring events posts
	 * if both parameters are true, the function brings all messages and then we have to use isInstanceOf when
	 * to distinct between Post and EventPost
	 * @return
	 */
	public HashSet<Post> search(Location loc, Double distance, String username, 
			boolean bringPosts, boolean bringEvents) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		HashSet<Post> postsSet = new HashSet<Post>();
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			// bring events
			if(bringEvents){
				String strStatement="";
				strStatement += "SELECT username,m.mid,mdate,content,latnum,longnum,maxregs " +
				"FROM messages m,event_reg_status e WHERE type=1 and m.mid=e.mid and " +
				"ST_DWithin(geotag,ST_GeographyFromText('POINT(" + loc.getLat() + " " + loc.getLong() + ")'),?)";
				
				// if username is not empty, get only messages of a specific user
				if(!username.equals("")){
					strStatement+=" and username=?";
				}
				
				statement = connection.prepareStatement(strStatement);
				
				statement.setDouble(1, distance);
				if(!username.equals("")){
					statement.setString(2, username);
				}
				rs = statement.executeQuery();
				
				// create events
				while(rs.next()){
					EventPost event = new EventPost(rs.getString("content"), rs.getString("username"),
							new Location(rs.getDouble("latnum"),
							rs.getDouble("longnum")),rs.getDate("mdate"),rs.getInt("maxregs"));
					event.setPostID(rs.getInt("mid"));
					postsSet.add(event);
				}
				rs.close();
				
				// add consensuses
				for(Post post : postsSet){
					EventPost event = (EventPost)post;
					statement = connection.prepareStatement("SELECT consid,conscontent,isapproved FROM consensuses " +
							"WHERE mid=?");
					statement.setInt(1, event.getPostID());
					rs = statement.executeQuery();
					
					while(rs.next()){
						Consensus cons = new Consensus(rs.getBoolean("isapproved"), rs.getString("conscontent"));
						cons.setConsID(rs.getInt("consid"));
						cons.setEventID(event.getPostID());
						event.addConsensus(cons);
					}
					rs.close();
				}
			}
			
			// bring regular posts
			if(bringPosts){
				String strStatement="";
				strStatement += "SELECT username,mid,mdate,content,latnum,longnum " +
				"FROM messages WHERE type=0 and " +
				"ST_DWithin(geotag,ST_GeographyFromText('POINT(" + loc.getLat() + " " + loc.getLong() + ")'),?)";
				
				// if username is not empty, get only messages of a specific user
				if(!username.equals("")){
					strStatement+=" and username=?";
				}
				
				statement = connection.prepareStatement(strStatement);
				
				statement.setDouble(1, distance);
				if(!username.equals("")){
					statement.setString(2, username);
				}
				rs = statement.executeQuery();
				
				// create events
				while(rs.next()){
					Post post = new Post(rs.getString("content"), rs.getString("username"),
							new Location(rs.getDouble("latnum"),
							rs.getDouble("longnum")),rs.getDate("mdate"));
					post.setPostID(rs.getInt("mid"));
					postsSet.add(post);
				}
				rs.close();
			}
			
			return postsSet;
			
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {					
					e.printStackTrace();
				}	
			}
		}
	}
	
	
	
	
	/**
	 * returns true if the user approved the consensus and false if not
	 * @param userName
	 * @param consID
	 * @return
	 */
	public boolean isConsensusApprovedByUser(String userName, int consID){
		ResultSet rs = null;
		PreparedStatement statement = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("SELECT username FROM consensus_accepts WHERE username=? " +
					"AND consid=?");
			statement.setString(1, userName);
			statement.setInt(2, consID);
			rs = statement.executeQuery();
	
			// check if there is selection
			if(rs.next()){
				return true;
			} else {
				return false;
			}
	
		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		} finally{
			try {
				//close resultset
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				//close statement
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//return connection to the pool
			if(null!=connection){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
	
	/*
	 * This function handles the approval status of the consensuses
	 * It checks if they have 0 acceptances (therefore need to be not approved), or enough acceptances
	 * to be approved.
	 * If the method fails, it doesn't throw an exception, but simply doesn't change the status of the
	 * consensuses. 
	 * 
	 * @param consIDs - a string in the format: "consID1,consID2,..."
	 * 
	 * We divided the methods that affect consensuses to two parts:
	 * The first called method does all "important" things (accepting consensus, removing a user etc...).
	 * Then the method calls this method that handles consensuses in another transaction.
	 * This is done so for scalability reasons: if we don't divide it that way, we will have to use SERAIZABLE
	 * transactions (see comment in function acceptConsensus), and this might be problematic when number of
	 * users grows. 
	 * Therefore, it is a legal (but very rare) state of the database to have 0 acceptances of
	 * a consensus, but the consensus is still approved, or have enough acceptances for approval but consensus
	 * is not approved. This state might be caused only if SQLException occurs.
	 */
	private void consensusStatusHandler(String consIDs) {
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet rs = null;
		Connection connection = null;
		
		try {
			connection = InitListener.getConnection();
			connection.setAutoCommit(false);
		
			if(consIDs==null || consIDs.isEmpty()){
				return; 
			}
			
			//start transaction
			connection.createStatement().execute("begin");

			// handle all consensuses that have to become not approved
			statement1 = connection.prepareStatement("UPDATE consensuses c SET isapproved=false " +
					"WHERE c.consid IN(?) and " +
					"(SELECT count(*) FROM consensus_accepts ca WHERE ca.consid=c.consid)=0");			
			statement1.setString(1,consIDs);
			
			// handle all consensuses that have to become approved
			statement2 = connection.prepareStatement("UPDATE consensuses c SET isapproved=true " +
					"WHERE c.consid IN(?) and " +
					"(SELECT count(*) FROM consensus_accepts ca WHERE ca.consid=c.consid)>=" +
					"(SELECT currentregs FROM event_reg_status e WHERE e.mid=c.mid)");			
			statement2.setString(1,consIDs);
			
			// commit changes
			statement1.executeUpdate();
			statement2.executeUpdate();
			connection.createStatement().execute("commit");

		} catch (SQLException e) {
			try {
				connection.createStatement().execute("rollback");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally{
			try {
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement1!=null)
					statement1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(statement2!=null)
					statement2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//return connection to the pool
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			}
		}
	}
	
}
